﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using AcctrueWMS.Foundation.DAL;
using System.Data.SqlClient;
namespace AcctruePMS.Bll.Manager
{
    public class WeekReportManager
    {
        /// <summary>
        /// 根据权限获取对应的周报
        /// </summary>
        /// <param name="userNo"></param>
        /// <returns></returns>
        public DataTable GetWeekReportByPower(string userNo)
        {

            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

            string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人

            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
            if (num > 0 || (dt != null && dt.Rows.Count > 0))
            {//总经理 和副总经理的权限或者属于行业线负责人,查询所有周报情况
               // string sql = "select A.weekReportGUID, A.userNo,userInfo.departWBS,A.startDate, A.endDate, A.reportStatus from ((select weekReportGUID, userNo, startDate, endDate, reportStatus from dbo.weekReport where userNo=@userNo and reportStatus=0) union all (select weekReportGUID, userNo, startDate, endDate, reportStatus from dbo.weekReport)) as A inner join userInfo on(userInfo.userNo= A.userNo) left join departments on(userInfo.departWBS=departments.wbsCode) order by A.startDate desc;";
                ///
                //string sql = " select weekReportGUID, a.userNo, a.departWBS,startDate, endDate, isnull(reportStatus,0)reportStatus from userInfo as a left join dbo.weekReport as b  on a.userNo= b.userNo left join departments on(a.departWBS=departments.wbsCode) order by b.startDate desc;";
                StringBuilder sb = new StringBuilder();
                sb.Append("select A.weekReportGUID, A.userNo,userInfo.departWBS,A.startDate, A.endDate, A.reportStatus from ((select weekReportGUID, userNo, startDate, endDate, reportStatus from (select * from weekReport union");
                sb.Append(" select NEWID(),userNo,startDate,endDate,reportStatus from weekReport_snapshot ) as C)) as A inner join userInfo on(userInfo.userNo= A.userNo) left join departments on(userInfo.departWBS=departments.wbsCode) order by A.startDate desc;");

                return WMSqlExcuter.ExecuteDataTable(sb.ToString(), CommandType.Text, pars);

            }
            else
            {

                DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                if (dt1 != null && dt1.Rows.Count > 0)
                {//属于部门负责人
                    StringBuilder sb1 = new StringBuilder();
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        sb1.Append("'");
                        sb1.Append(dt1.Rows[i].ItemArray[1]);
                        sb1.Append("'");
                        if (i < dt1.Rows.Count - 1)
                        {
                            sb1.Append(",");
                        }
                    }
                    if (sb1.Length == 0) { sb1.Append("''"); }
                    string sql4 = "select A.weekReportGUID, A.userNo,userInfo.departWBS,A.startDate, A.endDate, A.reportStatus from ((select weekReportGUID, userNo, startDate, endDate, reportStatus from dbo.weekReport where userNo=@userNo and reportStatus=0) union all (select weekReportGUID, userNo, startDate, endDate, reportStatus from dbo.weekReport)) as A inner join userInfo on(userInfo.userNo= A.userNo) left join departments on(userInfo.departWBS=departments.wbsCode) where A.userNo in (select userNo from userInfo where departWBS in (" + sb1.ToString() + " ) ) or A.userNo=@userNo order by A.startDate  desc;";
                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {
                        return new DataTable();
                    }
                }
                else
                {//普通权限

                    string sql5 = "select weekReportGUID,userInfo.departWBS,userInfo.userNo, startDate, endDate, reportStatus from dbo.weekReport inner join userInfo on(userInfo.userNo= dbo.weekReport.userNo) left join departments on(userInfo.departWBS=departments.wbsCode) where  userInfo.userNo=@userNo order by startDate  desc;";
                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {
                        return new DataTable();
                    }
                }
            }

        }

        public DataTable GetWeekReportByCondition(string CurrentUserNo, string userNo, string departement, string reportStatus, string startDate1, string endDate1, string startDate2, string endDate2)
        {

            DataTable dt1 = GetWeekReportByPower(CurrentUserNo);
            DataTable dt2 = dt1.Clone();
            DataRow[] drs = null;
            StringBuilder sb = new StringBuilder();
            sb.Append("1=1");
            if (userNo != string.Empty)
            {
                sb.AppendFormat(" and userNo='{0}'", userNo);
            }
            if (departement != string.Empty)
            {
                sb.AppendFormat(" and departWBS='{0}'", departement);
            }

            if (reportStatus != string.Empty)
            {
                sb.AppendFormat(" and reportStatus='{0}'", reportStatus);
            }
            //if (startDate1 != string.Empty && startDate2 != string.Empty)
            //{
            //    sb.AppendFormat(" and startDate between '{0}' and '{1}'", startDate1, startDate2);
            //}
            //if (endDate1 != string.Empty && endDate2 != string.Empty)
            //{
            //    sb.AppendFormat(" and endDate between '{0}' and '{1}'", endDate1, endDate2);
            //}
            if (startDate1!=string.Empty)
            {
                sb.AppendFormat(" and startDate >='{0}'", startDate1);
            }
            if (endDate1!=string.Empty)
            {
               sb.AppendFormat(" and endDate >='{0}'",endDate1); 
            }
            if (startDate2!= string.Empty)
            {
                sb.AppendFormat("and startDate <='{0}'", startDate2);
            }
            if (endDate2 != string.Empty)
            {
                sb.AppendFormat("and endDate <='{0}'", endDate2);
            }
            drs = dt1.Select(sb.ToString());
            foreach (DataRow dr in drs)
            {
                DataRow dr2 = dt2.NewRow();
                dr2.ItemArray = dr.ItemArray;
                dt2.Rows.Add(dr2);
            }
            dt2.Columns.Add("reportStatusText");
            foreach (DataRow dr in dt2.Rows)
            {
                dr["departWBS"] = WMSqlExcuter.ExecuteRealDataTable(string.Format("SELECT  departName FROM departments  WHERE wbsCode='{0}'", dr["departWBS"].ToString())).Rows.Count==0 ? dr["departWBS"].ToString() : WMSqlExcuter.ExecuteRealDataTable(string.Format("SELECT  departName FROM departments  WHERE wbsCode='{0}'", dr["departWBS"].ToString())).Rows[0][0];
                dr["userNo"] = WMSqlExcuter.ExecuteRealDataTable(string.Format("SELECT  displayName FROM userInfo  WHERE userNo='{0}'", dr["userNo"])).Rows.Count==0 ? dr["userNo"] : WMSqlExcuter.ExecuteRealDataTable(string.Format("SELECT  displayName FROM userInfo  WHERE userNo='{0}'", dr["userNo"])).Rows[0][0];

                dr["reportStatusText"] = dr["reportStatus"].ToString() =="0" ? "未提交" : "提交";
            }
            return dt2;
        }


    }
}

